﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Utility;
using Utility.Helper;

namespace BLL
{
    public class Sys_UserBLL : BaseBLL
    {
        public Sys_UserBLL(Service service)
        {
            this.Service = service;
        }

        public Sys_UserBLL()
        {
        }

        public DataTable GetRoleInfoById(string roleId, string moduleId)
        {
            string sql = string.Format(@"SELECT b.MODULE_ID d0,
       b.MODULE_NAME d1,
       '2' d2,
       a.MODULE_ID d3,
       a.MODULE_NAME d4,
       '1' d5,
       a.MODULE_PARENTID d6,
       '' d7,
       '0' d8,
       b.MODULE_PATH d9
  FROM THKQS.SYS_MODULE a, THKQS.SYS_MODULE b
 WHERE a.MODULE_ID = b.MODULE_PARENTID
   AND a.MODULE_STATE = 1
   AND b.MODULE_STATE = 1
   AND b.MODULE_ID IN
       (SELECT MODULE_ID
          FROM THKQS.Sys_RoleModule
         WHERE ROLE_ID IN
               (SELECT ROLE_ID
                  FROM THKQS.Sys_Role
                 WHERE Sys_Role.ROLE_STATE = 1
                   AND ',' || '{0}' || ',' LIKE '%,' || Sys_RoleModule.ROLE_ID || ',%'))
   AND b.module_id = {1}
 ORDER BY a.MODULE_PARENTID  DESC,
          a.MODULE_ORDERNUM1 ASC,
          b.MODULE_ORDERNUM2 ASC", roleId, moduleId);
            return Query(sql);
        }

        public DataTable GetInfoByCon(string Type, object param)
        {
            string sql = "SELECT sysu.USER_ID , sysu.USER_NAME , sysu.USER_LOGINNAME , sysu.USER_PWD , sysu.USER_ROLEID , DECODE(sysu.USER_JOBSTATUS,'0','离职''在职') AS USER_JOBSTATUS, DECODE(sysu.USER_SEX,'0','女''男') AS USER_SEX, sysu.USER_PHONE , DECODE(sysu.USER_LOGINSTATUS,'0','禁止登录''允许登录') AS USER_LOGINSTATUS, DECODE(sysu.USER_ONLINESTATUS,'0','离线''在线') AS USER_ONLINESTATUS, sysu.USER_LOGINTIME , sysu.USER_COMPANY , sysu.USER_DEPARTMENT , sysc.CMY_NAME , sysr.ROLE_NAME , sysd.DEPTNAME FROM THKQS.Sys_User sysu , THKQS.Sys_Company sysc , THKQS.Sys_Role sysr , THKQS.Sys_Department sysd WHERE INSTR(',' || CAST(sysr.ROLE_ID as VARCHAR2(100)) || ',', ',' || sysu.USER_ROLEID || ',') <> 0 AND sysu.USER_COMPANY = sysc.CMY_ID AND sysu.USER_DEPARTMENT = sysd.DEPTID ";
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');

                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND sysu.USER_NAME like '%" + str[0] + "%'";
                }
                if (!(string.IsNullOrEmpty(str[1]) || str[1] == "-1"))
                {
                    sql += " AND sysu.USER_COMPANY=" + str[1];
                }
                if (!(string.IsNullOrEmpty(str[2]) || str[2] == "-1"))
                {
                    sql += " AND sysu.USER_DEPARTMENT=" + str[2];
                }
                if (!(string.IsNullOrEmpty(str[3]) || str[3] == "-1"))
                {
                    sql += " AND sysu.USER_JOBSTATUS=" + str[3];
                }
                if (!(string.IsNullOrEmpty(str[4]) || str[4] == "-1"))
                {
                    sql += " AND sysu.USER_LOGINSTATUS=" + str[4];
                }
                if (!(string.IsNullOrEmpty(str[5]) || str[5] == "-1"))
                {
                    sql += " AND sysu.USER_ONLINESTATUS=" + str[5];
                }
                if (!(string.IsNullOrEmpty(str[6]) || str[6] == "-1"))
                {
                    sql += " AND sysu.USER_POSID=" + str[6];
                }

                sql = @" with cte(CMY_ID) as
(--父项
SELECT sysc.CMY_ID
FROM THKQS.Sys_Company sysc
WHERE sysc.CMY_ID =  '" + str[7] + @"'
UNION ALL
--递归结果集中的下级
SELECT t.CMY_ID
FROM THKQS.Sys_Company as t
INNER JOIN cte as c
ON t.CMY_PARENTID = c.CMY_ID)" + sql;
                sql += @" AND sysc.CMY_ID IN (SELECT CMY_ID FROM cte)";
            }
            if (Type == "LoginName")
            {
                sql += " AND sysu.USER_LOGINNAME='" + param.ToString() + "'";
            }
            if (Type == "COMPANYID")
            {
                sql = @" with cte(CMY_ID) as
(--父项
SELECT sysc.CMY_ID
FROM THKQS.Sys_Company sysc
WHERE sysc.CMY_ID =  '" + param + @"'
UNION ALL
--递归结果集中的下级
SELECT t.CMY_ID
FROM THKQS.Sys_Company as t
INNER JOIN cte as c
ON t.CMY_PARENTID = c.CMY_ID)" + sql;
                sql += @" AND sysc.CMY_ID IN (SELECT CMY_ID FROM cte)";
            }
            if (Type == "UserIds")
            {
                sql += " AND sysu.USER_ID IN(" + param.ToString() + ")";
            }
            sql += @"GROUP BY sysu.USER_ID,
                    sysu.USER_NAME,
                    sysu.USER_LOGINNAME,
                    sysu.USER_PWD,
                    sysu.USER_ROLEID,
                    USER_JOBSTATUS,
                    USER_SEX,
                    sysu.USER_PHONE,
                    USER_LOGINSTATUS,
                    USER_ONLINESTATUS,
                    sysu.user_logintime,
                    sysu.USER_COMPANY,
                    sysu.USER_DEPARTMENT,
                    sysc.CMY_NAME,
                    sysr.ROLE_NAME,
                    sysd.DEPTNAME,
				    sysu.USER_POSID
                    ORDER BY sysu.USER_ID";
            DataTable dt = Query(sql);
            return dt;
        }

        public List<Sys_User> GetUsers(string ids)
        {
            if (ids == "")
            {
                return new List<Sys_User>();
            }
            var sql = string.Format("SELECT USER_ID,USER_NAME,USER_LOGINNAME,USER_PWD,USER_ROLEID,USER_JOBSTATUS,USER_SEX,USER_PHONE,USER_LOGINSTATUS,USER_ONLINESTATUS,USER_LOGINTIME,USER_COMPANY,USER_DEPARTMENT FROM SYS_USER WHERE USER_ID IN ({0})", ids);
            var data = new List<Sys_User>();
            foreach (DataRow item in Query(sql).Rows)
            {
                var model = new Sys_User();
                model.USER_ID = decimal.Parse(item["USER_ID"].ToString());
                model.USER_NAME = item["USER_NAME"].ToString();
                model.USER_LOGINNAME = item["USER_LOGINNAME"].ToString();
                model.USER_PWD = item["USER_PWD"].ToString();
                model.USER_ROLEID = item["USER_ROLEID"].ToString();
                model.USER_JOBSTATUS = item["USER_JOBSTATUS"].ToString();
                model.USER_SEX = item["USER_SEX"].ToString();
                model.USER_PHONE = item["USER_PHONE"].ToString();
                //model.IDCardNumber = dt.Rows[0]["IDCardNumber"].ToString();
                model.USER_LOGINSTATUS = item["USER_LOGINSTATUS"].ToString();
                model.USER_ONLINESTATUS = item["USER_ONLINESTATUS"].ToString();
                model.USER_LOGINTIME = DateTime.Parse(item["user_logintime"].ToString());
                model.USER_COMPANY = decimal.Parse(item["USER_COMPANY"].ToString());
                model.USER_DEPARTMENT = decimal.Parse(item["USER_DEPARTMENT"].ToString());
                data.Add(model);
            }
            return data;
        }

        public List<Sys_User> GetUsers(string[] idarry)
        {
            var ids = "";
            foreach (var item in idarry)
            {
                ids += item + ",";
            }
            if (ids != "")
            {
                ids = ids.Substring(0, ids.Length - 1);
                return GetUsers(ids);
            }
            else
            {
                return new List<Sys_User>();
            }
        }

        public DataTable GetUsersByDataTable(string ids)
        {
            var sql = "SELECT * FROM SYS_USER WHERE USER_ID IN(" + ids + ")";
            return Query(sql);
        }

        public Sys_User GetInfoById(string UId)
        {
            string sql = string.Format("SELECT * FROM SYS_USER WHERE USER_ID=" + UId);

            DataTable dt = Query(sql);
            Sys_User model = new Sys_User();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.USER_ID = decimal.Parse(dt.Rows[0]["USER_ID"].ToString());
                model.USER_NAME = dt.Rows[0]["USER_NAME"].ToString();
                model.USER_LOGINNAME = dt.Rows[0]["USER_LOGINNAME"].ToString();
                model.USER_PWD = dt.Rows[0]["USER_PWD"].ToString();
                model.USER_ROLEID = dt.Rows[0]["USER_ROLEID"].ToString();
                model.USER_JOBSTATUS = dt.Rows[0]["USER_JOBSTATUS"].ToString();
                model.USER_SEX = dt.Rows[0]["USER_SEX"].ToString();
                model.USER_PHONE = dt.Rows[0]["USER_PHONE"].ToString();
                model.IDCardNumber = dt.Rows[0]["IDCardNumber"].ToString();
                model.USER_LOGINSTATUS = dt.Rows[0]["USER_LOGINSTATUS"].ToString();
                model.USER_ONLINESTATUS = dt.Rows[0]["USER_ONLINESTATUS"].ToString();
                model.USER_LOGINTIME = DateTime.Parse(dt.Rows[0]["user_logintime"].ToString());
                model.USER_COMPANY = decimal.Parse(dt.Rows[0]["USER_COMPANY"].ToString());
                model.USER_DEPARTMENT = decimal.Parse(dt.Rows[0]["USER_DEPARTMENT"].ToString());
                model.C_REMARK = dt.Rows[0]["C_REMARK"].ToString();
                //model.USER_POSID = decimal.Parse(dt.Rows[0]["USER_POSID"].ToString() == "" ? "0" : dt.Rows[0]["USER_POSID"].ToString());
            }
            return model;
        }

        public Sys_User Find(decimal id)
        {
            return GetInfoById(id.ToString().Trim());
        }

        public Sys_User GetInfoByLogin(string loginName)
        {
            string sql = string.Format(@"SELECT * FROM Sys_User sysu WHERE sysu.USER_LOGINNAME='" + loginName + "'");

            DataTable dt = Query(sql);

            //DataTable dt = DbHelperSQL.GetDataTable(sql);
            Sys_User model = new Sys_User();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.USER_ID = decimal.Parse(dt.Rows[0]["USER_ID"].ToString());
                model.USER_NAME = dt.Rows[0]["USER_NAME"].ToString();
                model.USER_LOGINNAME = dt.Rows[0]["USER_LOGINNAME"].ToString();
                model.USER_PWD = dt.Rows[0]["USER_PWD"].ToString();
                model.USER_ROLEID = dt.Rows[0]["USER_ROLEID"].ToString();
                model.USER_JOBSTATUS = dt.Rows[0]["USER_JOBSTATUS"].ToString();
                model.USER_SEX = dt.Rows[0]["USER_SEX"].ToString();
                model.USER_PHONE = dt.Rows[0]["USER_PHONE"].ToString();
                model.USER_LOGINSTATUS = dt.Rows[0]["USER_LOGINSTATUS"].ToString();
                model.USER_ONLINESTATUS = dt.Rows[0]["USER_ONLINESTATUS"].ToString();
                model.USER_LOGINTIME = DateTime.Parse(dt.Rows[0]["user_logintime"].ToString());
                model.USER_COMPANY = decimal.Parse(dt.Rows[0]["USER_COMPANY"].ToString());
                model.USER_DEPARTMENT = decimal.Parse(dt.Rows[0]["USER_DEPARTMENT"].ToString());
                //model.USER_POSID = decimal.Parse(dt.Rows[0]["USER_POSID"].ToString() == "" ? "0" : dt.Rows[0]["USER_POSID"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        public bool GetIsLogin(decimal userid)
        {
            string sql = "SELECT * FROM  Sys_User t WHERE t.USER_LOGINSTATUS=1 AND USER_ID=" + userid + "";

            DataTable dt = Query(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新用户登录状态
        /// </summary>
        /// <param name="b">是否为登录 True 登录 False 离线</param>
        /// <param name="userid">用户ID</param>
        /// <param name="operatename">用户名</param>
        /// <returns></returns>
        public bool UpdateLogin(bool b, decimal userid, string operatename)
        {
            var entity = GetInfoById(userid.ToString().Trim());
            if (b)
            {
                entity.USER_ONLINESTATUS = "1";
                entity.USER_LOGINTIME = DateTime.Now;
            }
            else
            {
                entity.USER_ONLINESTATUS = "0";
            }
            if (UPDATE(entity, operatename) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int INSERT(Sys_User model, string operName)
        {
            model.USER_ID = GetSequence("USER_ID");
            var sql = string.Format(@"INSERT INTO SYS_USER(
	USER_ID,
	USER_NAME,
	USER_LOGINNAME,
	USER_PWD,
	USER_ROLEID,
	USER_JOBSTATUS,
	USER_SEX,
	USER_PHONE,
	USER_LOGINSTATUS,
	USER_ONLINESTATUS,
	USER_LOGINTIME,
	USER_COMPANY,
	USER_DEPARTMENT
)
VALUES (
	{0},
	'{1}',
	'{2}',
	'{3}',
	'{4}',
	'{5}',
	'{6}',
	'{7}',
	'{8}',
	'{9}',
	TO_DATE('{10}','YYYY-MM-DD HH24:MI:SS'),
	{11},
	{12}
)", model.USER_ID,
   model.USER_NAME,
   model.USER_LOGINNAME,
   model.USER_PWD,
   model.USER_ROLEID,
   model.USER_JOBSTATUS,
   model.USER_SEX,
   model.USER_PHONE,
   model.USER_LOGINSTATUS,
   model.USER_ONLINESTATUS,
   model.USER_LOGINTIME.ToString("yyyy-MM-dd HH:mm:ss"),
   model.USER_COMPANY,
   model.USER_DEPARTMENT
   );
            return NoQuery(sql, operName);
        }

        public bool Insert(Sys_User entity, string operName)
        {
            return INSERT(entity, operName) > 0 ? true : false;
        }

        public int UPDATE(Sys_User model, string operName)
        {
            var sql = string.Format(@"UPDATE SYS_USER
SET
	USER_NAME = '{1}',
	USER_LOGINNAME = '{2}',
	USER_PWD = '{3}',
	USER_ROLEID= '{4}',
	USER_JOBSTATUS = '{5}',
	USER_SEX = '{6}',
	USER_PHONE = '{7}',
	USER_LOGINSTATUS = '{8}',
	USER_ONLINESTATUS = '{9}',
	USER_LOGINTIME = TO_DATE('{10}','YYYY-MM-DD HH24:MI:SS'),
	USER_COMPANY = {11},
	USER_DEPARTMENT = {12},
    IDCardNumber = '{13}',
    C_REMARK = '{14}'
WHERE
	USER_ID = {0}",
                   model.USER_ID,
                   model.USER_NAME,
                   model.USER_LOGINNAME,
                   model.USER_PWD,
                   model.USER_ROLEID,
                   model.USER_JOBSTATUS,
                   model.USER_SEX,
                   model.USER_PHONE,
                   model.USER_LOGINSTATUS,
                   model.USER_ONLINESTATUS,
                   model.USER_LOGINTIME.ToString("yyyy-MM-dd HH:mm:ss"),
                   model.USER_COMPANY,
                   model.USER_DEPARTMENT,
                   model.IDCardNumber,
                   model.C_REMARK);
            return NoQuery(sql, operName);

            /*
            string sql = string.Format(@"UPDATE THKQS.Sys_User SET Sys_User.USER_NAME = '{0}', Sys_User.USER_LOGINNAME = '{1}', Sys_User.USER_PWD = '{2}', Sys_User.USER_ROLEID = '{3}', Sys_User.USER_JOBSTATUS = '{4}', Sys_User.USER_SEX = '{5}', Sys_User.USER_PHONE = '{6}', Sys_User.USER_LOGINSTATUS = '{7}', Sys_User.USER_ONLINESTATUS = '{8}', Sys_User.USER_LOGINTIME = '{9}', Sys_User.USER_COMPANY = '{10}', Sys_User.USER_DEPARTMENT = '{11}' WHERE Sys_User.USER_ID = '{12}'", model.USER_NAME, model.USER_LOGINNAME, model.USER_PWD, model.USER_ROLEID, model.USER_JOBSTATUS, model.USER_SEX, model.USER_PHONE, model.USER_LOGINSTATUS, model.USER_ONLINESTATUS, model.USER_LOGINTIME, model.USER_COMPANY, model.USER_DEPARTMENT, model.USER_ID);
            int count = DbHelperSQL.ExecuteSqlLog(sql, operName);
            return count;
            */
        }

        public bool Update(Sys_User entity, string operName)
        {
            return UPDATE(entity, operName) > 0 ? true : false;
        }

        public int DELETE(string ids, string operName)
        {
            string sql = string.Format(@"DELETE FROM Sys_User WHERE USER_ID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public int isExit(string LoginName)
        {
            string sql = @"SELECT count(1) hs FROM  Sys_User WHERE USER_LOGINNAME='" + LoginName + "'";
            DataTable dt = Query(sql);
            int names = 0;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    names = Convert.ToInt32(dr["hs"]);
                }
            }

            return Convert.ToInt32(names);
        }

        public int isExit(string uName, string did)
        {
            string sql = @"SELECT * FROM SYS_USER WHERE USER_DEPARTMENT=" + did + " AND USER_NAME='" + uName + "'";
            DataTable dt = Query(sql);
            int id = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                id = Convert.ToInt32(dt.Rows[0]["USER_ID"]);
            }
            return id;
        }

        #region 修改登陆状态为下线

        public int off(string id, string operaName)
        {
            if (UpdateLogin(false, Convert.ToDecimal(id), operaName))
            {
                return 1;
            }
            return 0;
        }

        #endregion 修改登陆状态为下线

        #region 查询在线人数

        public int findCount()
        {
            string sql = "SELECT COUNT(*) FROM SYS_USER WHERE USER_ONLINESTATUS =1";
            return GetCount(sql);
        }

        #endregion 查询在线人数

        public DataTable GetInfoByComId(string comId)
        {
            string sql = "SELECT T1.USER_ID, T1.USER_NAME, T1.USER_LOGINNAME, T1.USER_PWD, T1.USER_ROLEID, DECODE ( T1.USER_JOBSTATUS, '1', '在职', '0', '离职', '在职' ) AS USER_JOBSTATUS, T1.USER_PHONE, DECODE ( T1.USER_SEX, '1', '男', '0', '女', '男' ) AS USER_SEX, DECODE ( T1.USER_LOGINSTATUS, '1', '允许登录', '0', '禁止登录', '允许登录' ) AS USER_LOGINSTATUS, DECODE ( T1.USER_ONLINESTATUS, '1', '在线', '0', '离线', '在线' ) AS USER_ONLINESTATUS, T1.USER_LOGINTIME, T1.USER_COMPANY, T1.USER_DEPARTMENT, T2.CMY_NAME, T3.ROLE_NAME, T4.DEPTNAME FROM THKQS.SYS_USER T1, THKQS.SYS_COMPANY T2, THKQS.SYS_ROLE T3, THKQS.SYS_DEPARTMENT T4 WHERE Instr ( ',' || T1.USER_ROLEID || ',', ',' || T3.ROLE_ID || ',' ) <> 0 AND T1.USER_COMPANY = T2.CMY_ID AND T1.USER_DEPARTMENT = T4.DEPTID";
            if (!string.IsNullOrEmpty(comId) && comId != "1")
            {
                sql += " AND T1.USER_COMPANY =" + comId;
            }
            sql += " GROUP BY T1.USER_ID, T1.USER_NAME, T1.USER_LOGINNAME, T1.USER_PWD, T1.USER_ROLEID, USER_JOBSTATUS, USER_SEX, T1.USER_PHONE, USER_LOGINSTATUS, USER_ONLINESTATUS, T1.user_logintime, T1.USER_COMPANY, T1.USER_DEPARTMENT, T2.CMY_NAME, T3.ROLE_NAME, T4.DEPTNAME ORDER BY T1.USER_ID";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetNameByCmyId(string cmyId)
        {
            /*string sql = @"SELECT T1.USER_ID,
       T1.USER_NAME,
       T1.USER_LOGINNAME,
       T1.USER_PWD,
       T1.USER_ROLEID,
       DECODE(T1.USER_JOBSTATUS, '1', '在职', '0', '离职', '在职') AS USER_JOBSTATUS,
       T1.USER_PHONE,
       DECODE(T1.USER_SEX, '1', '男', '0', '女', '男') AS USER_SEX,
       DECODE(T1.USER_LOGINSTATUS,
              '1',
              '允许登录',
              '0',
              '禁止登录',
              '允许登录') AS USER_LOGINSTATUS,
       DECODE(T1.USER_ONLINESTATUS, '1', '在线', '0', '离线', '在线') AS USER_ONLINESTATUS,
       T1.USER_LOGINTIME,
       T1.USER_COMPANY,
       T1.USER_DEPARTMENT,
       T2.CMY_NAME,
       T3.ROLE_NAME,
       T4.DEPTNAME
  FROM SYS_USER       T1,
       SYS_COMPANY    T2,
       SYS_ROLE       T3,
       SYS_DEPARTMENT T4
 WHERE Instr(',' || T1.USER_ROLEID || ',', ',' || T3.ROLE_ID || ',') <> 0
   AND T1.USER_COMPANY = T2.CMY_ID
   AND T1.USER_DEPARTMENT = T4.DEPTID
   AND T2.CMY_ID=" + cmyId;*/
            string sql = @"SELECT T1.USER_ID,
       T1.USER_NAME,
       T1.USER_LOGINNAME,
       T1.USER_PWD,
       T1.USER_ROLEID,
       DECODE(T1.USER_JOBSTATUS, '1', '在职', '0', '离职', '在职') AS USER_JOBSTATUS,
       T1.USER_PHONE,
       DECODE(T1.USER_SEX, '1', '男', '0', '女', '男') AS USER_SEX,
       DECODE(T1.USER_LOGINSTATUS,
              '1',
              '允许登录',
              '0',
              '禁止登录',
              '允许登录') AS USER_LOGINSTATUS,
       DECODE(T1.USER_ONLINESTATUS, '1', '在线', '0', '离线', '在线') AS USER_ONLINESTATUS,
       T1.USER_LOGINTIME,
       T1.USER_COMPANY,
       T1.USER_DEPARTMENT,
       T2.CMY_NAME,
       T3.ROLE_NAME,
       T4.DEPTNAME
  FROM SYS_USER T1, SYS_COMPANY T2, SYS_ROLE T3, SYS_DEPARTMENT T4
 WHERE Instr(',' || T1.USER_ROLEID || ',', ',' || T3.ROLE_ID || ',') <> 0
   AND T1.USER_COMPANY = T2.CMY_ID
   AND T1.USER_DEPARTMENT = T4.DEPTID
   AND T2.CMY_ID IN (SELECT CMY_ID
                       FROM SYS_COMPANY T
                      START WITH CMY_ID = " + cmyId + @"
                     CONNECT BY PRIOR CMY_ID = CMY_PARENTID)";
            DataTable dt = Query(sql);
            return dt;
        }

        public string GetUserNameS(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT USER_NAME FROM SYS_USER");
            if (!string.IsNullOrEmpty(ids))
            {
                strSql.Append(" WHERE USER_ID IN (" + ids + ") ");
            }
            DataTable dt = Query(strSql.ToString());
            string names = "";
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    names += dr["USER_NAME"].ToString() + ",";
                }
            }
            if (names.Length > 0)
            {
                return names.Substring(0, names.LastIndexOf(','));
            }
            return "";
        }

        public DataTable GetDataUser(string ids)
        {
            string sql = "SELECT * FROM Sys_User t WHERE t.USER_ID IN (" + ids + ")";

            DataTable dt = Query(sql);
            return dt;//DbHelperSQL.GetDataTable(sql);
        }

        public DataTable GetAllUser(string whereStr)
        {
            string sql = string.Format(@"SELECT * FROM SYS_USER u WHERE 1=1 ");
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            sql += " ORDER BY u.USER_ID ";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetListByPage(int usercompanyid, string whereStr, string orderbyStr, int startIndex, int endIndex, out int count)
        {
            #region Sql

            var sql = string.Format(@"SELECT
                                        TT1.USER_ID,
                                        TT1.USER_NAME,
                                        TT1.USER_LOGINNAME,
                                        TT2.CMY_NAME,
                                        TT2.DEPTNAME,
                                        TT1.USER_PHONE,
                                        TT1.USER_JOBSTATUS,
                                        TT1.USER_LOGINSTATUS,
                                        TT1.USER_COMPANY,
                                        TT1.USER_ROLEID,
       listagg(T2.ROLE_NAME, ',') within GROUP(order by TT1.USER_ROLEID) AS ROLE_NAMES
                                    FROM SYS_USER TT1
                                    LEFT JOIN ( SELECT *
                                                 FROM ( SELECT
                                                            CMY_ID,
                                                            CMY_NAME,
                                                            CMY_NAME2,
                                                            CMY_LEVEL,
                                                            CMY_TYPE,
                                                            CMY_PARENTID
                                                        FROM SYS_COMPANY
                                                        START WITH CMY_ID = {0} CONNECT BY PRIOR CMY_ID = CMY_PARENTID ) T1
                                                 LEFT JOIN SYS_DEPARTMENT T2 ON T1.CMY_ID = T2.CMY_ID ) TT2 ON TT1.USER_DEPARTMENT = TT2.DEPTID
 INNER JOIN sys_role T2
    ON ',' || TT1.USER_ROLEID || ',' LIKE
       '%,' || to_char(T2.ROLE_ID) || ',%'", usercompanyid);

            #endregion Sql

            if (whereStr != null)
            {
                sql += " " + whereStr;
            }
            sql += @" group by TT1.USER_ID,
          TT1.USER_NAME,
          TT1.USER_LOGINNAME,
          TT2.CMY_NAME,
          TT2.DEPTNAME,
          TT1.USER_PHONE,
          TT1.USER_JOBSTATUS,
          TT1.USER_LOGINSTATUS,
          TT1.USER_COMPANY,
          TT1.USER_ROLEID";
            if (orderbyStr != null)
            {
                sql += " " + orderbyStr;
            }
            count = GetCount(string.Format("SELECT Count(*) FROM ({0})", sql));
            return PageQuery(sql, startIndex, endIndex - startIndex);
            //var dt = Query( string.Format("SELECT USER_ID,USER_NAME,USER_LOGINNAME,CMY_NAME,DEPTNAME,USER_PHONE,USER_JOBSTATUS,USER_LOGINSTATUS,USER_COMPANY,USER_ROLEID FROM ({0}) WHERE TABLE_ROW>{1} AND TABLE_ROW<={2}", sql, startIndex, endIndex));
            //return dt;
        }

        public DataTable GetListByPage(string COMPANYID, string whereStr, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"with cte(CMY_ID) as
(--父项
SELECT sysc.CMY_ID
FROM THKQS.Sys_Company sysc
WHERE sysc.CMY_ID =  '" + COMPANYID + @"'
UNION ALL
--递归结果集中的下级
SELECT t.CMY_ID
FROM THKQS.Sys_Company as t
INNER JOIN cte as c
ON t.CMY_PARENTID = c.CMY_ID) ");
            strSql.Append(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY sysu." + orderby + @") as r,
    sysu.USER_ID ,
    sysu.USER_NAME ,
    sysu.USER_LOGINNAME ,
    sysu.USER_PWD ,
    sysu.USER_ROLEID ,
    CASE sysu.USER_JOBSTATUS
    WHEN '1' THEN '在职'
    WHEN '0' THEN '离职'
    END as USER_JOBSTATUS ,
    CASE sysu.USER_SEX
    WHEN '1' THEN '男'
    WHEN '0' THEN '女'
    END as USER_SEX ,
    sysu.USER_PHONE ,
    CASE sysu.USER_LOGINSTATUS
    WHEN '1' THEN '允许登录'
    WHEN '0' THEN '禁止登录'
    END as USER_LOGINSTATUS ,
    CASE sysu.USER_ONLINESTATUS
    WHEN '1' THEN '在线'
    WHEN '0' THEN '离线'
    END as USER_ONLINESTATUS ,
    sysu.USER_LOGINTIME ,
    sysu.USER_COMPANY ,
    sysu.USER_DEPARTMENT ,
    sysc.CMY_NAME ,
    --sysr.ROLE_NAME ,
    sysd.DEPTNAME,
    sysu.USER_POSID--,
    --jxp.C_positionName
FROM
    THKQS.Sys_User sysu ,
    THKQS.Sys_Company sysc ,
    --THKQS.Sys_Role sysr ,
    THKQS.Sys_Department sysd--,
    --Jx_Position jxp
WHERE --CHARINDEX(',' + CAST(sysr.ROLE_ID as VARCHAR) + ',',
    --',' + sysu.USER_ROLEID + ',') <> 0 AND
    sysu.USER_COMPANY = sysc.CMY_ID
    AND sysu.USER_DEPARTMENT = sysd.DEPTID
    --AND sysu.USER_POSID=jxp.N_positionId
");
            if (!string.IsNullOrEmpty(whereStr.Trim()))
            {
                strSql.Append(whereStr);
            }
            strSql.Append(@" ) TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return Query(strSql.ToString());
        }

        public bool GetRole(string uid, string rid)
        {
            if (string.IsNullOrEmpty(rid))
            {
                return false;
            }
            var entity = GetInfoById(uid);
            if (entity == null)
            {
                return false;
            }
            if (entity.USER_ROLEID.Split(',').SingleOrDefault(a => a == rid) == null)
            {
                return false;
            }
            return true;
        }

        public bool GetKLD(string uid)
        {
            string sql = @"SELECT * FROM Sys_User u WHERE u.USER_ID=" + uid + " AND u.USER_DEPARTMENT=200";
            DataTable dt = Query(sql);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查询所有系统用户信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllInfo()
        {
            DataTable dt = (DataTable)HttpRuntimeCache.Get("SYS_USER_INFO");
            if (dt == null)
            {
                string sql = string.Format(@"select sysu.user_id,
       sysu.user_name,
       sysu.user_loginname,
       sysu.user_pwd,
       sysu.user_roleid,
       decode(sysu.user_jobstatus, '1', '在职', '离职') user_jobstatus,
       decode(sysu.user_sex, '1', '男', '女') user_sex,
       sysu.user_phone,
       decode(sysu.user_loginstatus, 1, '允许登录', '禁止登录') user_loginstatus,
       decode(sysu.user_onlinestatus, 1, '在线', '离线') user_onlinestatus,
       sysu.user_logintime,
       sysu.user_company,
       sysu.USER_DEPARTMENT,
       sysc.cmy_name,
       wmsys.wm_concat(sysr.role_name) role_name,
       sysd.deptname
  from sys_user sysu, sys_company sysc, sys_role sysr, sys_department sysd
 where instr(','||sysu.user_roleid||',',','||sysr.role_id||',')<>0
   and sysu.user_company = sysc.cmy_id
   and sysu.user_department = sysd.deptid
   group by sysu.user_id,
       sysu.user_name,
       sysu.user_loginname,
       sysu.user_pwd,
       sysu.user_roleid,
       user_jobstatus,
       user_sex,
       sysu.user_phone,
       user_loginstatus,
       user_onlinestatus,
       sysu.user_logintime,
       sysu.user_company,
       sysu.USER_DEPARTMENT,
       sysc.cmy_name,
       sysd.deptname
 order by sysu.user_id desc");
                dt = Query(sql);
                HttpRuntimeCache.Set("SYS_USER_INFO", dt, 10, false);
            }
            return dt;
        }

        public List<Sys_User> GetAllUsers()
        {
            var sql = string.Format("SELECT USER_ID,USER_NAME,USER_LOGINNAME,USER_PWD,USER_ROLEID,USER_JOBSTATUS,USER_SEX,USER_PHONE,USER_LOGINSTATUS,USER_ONLINESTATUS,USER_LOGINTIME,USER_COMPANY,USER_DEPARTMENT FROM SYS_USER ");
            var data = new List<Sys_User>();
            foreach (DataRow item in Query(sql).Rows)
            {
                var model = new Sys_User();
                model.USER_ID = decimal.Parse(item["USER_ID"].ToString());
                model.USER_NAME = item["USER_NAME"].ToString();
                model.USER_LOGINNAME = item["USER_LOGINNAME"].ToString();
                model.USER_PWD = item["USER_PWD"].ToString();
                model.USER_ROLEID = item["USER_ROLEID"].ToString();
                model.USER_JOBSTATUS = item["USER_JOBSTATUS"].ToString();
                model.USER_SEX = item["USER_SEX"].ToString();
                model.USER_PHONE = item["USER_PHONE"].ToString();
                //model.IDCardNumber = dt.Rows[0]["IDCardNumber"].ToString();
                model.USER_LOGINSTATUS = item["USER_LOGINSTATUS"].ToString();
                model.USER_ONLINESTATUS = item["USER_ONLINESTATUS"].ToString();
                model.USER_LOGINTIME = DateTime.Parse(item["user_logintime"].ToString());
                model.USER_COMPANY = decimal.Parse(item["USER_COMPANY"].ToString());
                model.USER_DEPARTMENT = decimal.Parse(item["USER_DEPARTMENT"].ToString());
                data.Add(model);
            }
            return data;
        }

        public string GetUserIdByCompanyId(int companyId)
        {
            //var sql = "SELECT T1.USER_ID FROM SYS_USER T1 WHERE T1.USER_JOBSTATUS = 1 AND T1.USER_COMPANY = " + companyId;
            var sql = @"SELECT
	DISTINCT T1.DEPT_PERSONID
FROM SYS_DEPARTMENT T1
WHERE
	T1.CMY_ID = {0} AND
	T1.DEPT_PERSONID IS NOT NULL";
            sql = string.Format(sql, companyId);
            var str = "";
            foreach (var item in Query(sql).AsEnumerable())
            {
                if (str != "")
                {
                    str += ",";
                }
                str += item.GetValue<string>("DEPT_PERSONID");
            }
            return str;
        }
    }
}